package Reports;


import java.awt.Component;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridBagConstraints;
import java.awt.Insets;
import java.awt.Toolkit;

import javax.swing.JButton;
import javax.swing.JComponent;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingConstants;

import TableAdditions.main;
import Utility.DatePicker;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.print.PageFormat;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JComboBox;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;



public class SortByProduct extends JPanel{
	private final JButton createTableButton = new JButton("Başla");
    DefaultTableModel model = new DefaultTableModel();
    JTable table = new JTable(model);
	public SortByProduct() {
		
  
			
		   	JLabel startLabel = new JLabel("Başlangıç Tarihi:");
		    final JTextField startText = new JTextField(10);
		    startText.setText("01-01-2010");
		    JButton startButton = new JButton("Göster");
		    JPanel startingDatePanel = new JPanel();
		    startingDatePanel.add(startLabel);
		    startingDatePanel.add(startText);
		    startingDatePanel.add(startButton);
		    
	        String[] columnNames = {"Tarih","Ürün", "Müşteri","B.Fiyat","Promosyon","Miktar","Toplam","Nakit","Kalan"};

	        for(int i=0;i<columnNames.length;i++)
	        	model.addColumn(columnNames[i]);	//add columns
	        
	        table.setPreferredScrollableViewportSize(new Dimension(920,550));
	        table.setFillsViewportHeight(true);
		    final JFrame startFrame = new JFrame();
		    add(startingDatePanel);
		    
		    startButton.addActionListener(new ActionListener() {
		        public void actionPerformed(ActionEvent ae) {
		            startText.setText(new DatePicker(startFrame).setPickedDate());
		        }
		    });
		   	JLabel finishLabel = new JLabel("Bitiş Tarihi:");
		    final JTextField finishText = new JTextField(10);
		    JButton finishButton = new JButton("Göster");
		    JPanel finishDatePanel = new JPanel();
		    
		    JLabel lblNewLabel = new JLabel("          ");
		    finishDatePanel.add(lblNewLabel);
		    finishDatePanel.add(finishLabel);
		    finishDatePanel.add(finishText);
		    finishDatePanel.add(finishButton);
		    final JFrame finishFrame = new JFrame();
		    add(finishDatePanel);
		    
		    finishButton.addActionListener(new ActionListener() {
		        public void actionPerformed(ActionEvent ae) {
		            finishText.setText(new DatePicker(finishFrame).setPickedDate());
		        }
		    });
		    
		    
		
		setLayout(new FlowLayout(FlowLayout.CENTER, 5, 5));
	 	Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        String[] mydata=null;
        int i=0,counter=0;
        try {
            con = (Connection) DriverManager.getConnection(url, user, password);
            st = (Statement) con.createStatement();
            rs=st.executeQuery("SELECT * from product");
           
            while(rs.next())
            	++counter;
            rs.first();
            mydata=new String[counter];

            do
            {
            	
            		mydata[i]=rs.getString("product_name");
            		++i;
        
            }while(rs.next());
            con.close();
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(main.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(main.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }

		
		final JComboBox productComboBox = new JComboBox(mydata);
		productComboBox.setMaximumRowCount(10);
		productComboBox.addActionListener(new ActionListener() {
			
			@Override
			public void actionPerformed(ActionEvent arg0) {
				
				
			}
		});
		
		JLabel lblNewLabel_1 = new JLabel("                  ");
		add(lblNewLabel_1);
		add(productComboBox);
		
		JLabel label = new JLabel("                           ");
		add(label);
		createTableButton.addActionListener(new ActionListener() {
			
			@Override
			public void actionPerformed(ActionEvent arg0) {
			    for( int i = model.getRowCount() - 1; i >= 0; i-- ) {
			        model.removeRow(i);
			    }
				Object product=productComboBox.getSelectedItem();
				String startingDate2 = startText.getText().replaceAll("(\\d+)-(\\d+)-(\\d+)", "$3-$2-$1");
				String finishDate2 = finishText.getText().replaceAll("(\\d+)-(\\d+)-(\\d+)", "$3-$2-$1");
			 	Connection con = null;
		        Statement st = null;
		        ResultSet rs = null;
		        ResultSet rs2 = null;
		       
		        String url = "jdbc:mysql://localhost:3306/mydb";
		        String user = "root";
		        String password = "qweasdzxc";
		        Object[][] mydata=null;
		        int i=0,counter=0;
		        try {
		            con = (Connection) DriverManager.getConnection(url, user, password);
		            st = (Statement) con.createStatement(); 
		          /*  rs=st.executeQuery("SELECT * from bill" +
		            		" WHERE product_id IN ( SELECT product_id from product WHERE product_name='"+ product + "') AND bill_date BETWEEN '"+startingDate2+"' AND '"+finishDate2+"' Order By bill_date") ;*/
		            rs=st.executeQuery("SELECT * from bill" +
		            		" WHERE ( product_id IN ( SELECT product_id from product WHERE product_name='"+product+"') AND (bill_date BETWEEN '"+startingDate2+"' AND '"+finishDate2+"') )" +
		            				" Order By bill_date") ;
		        if(rs.isBeforeFirst()==true){
		            while(rs.next())
		            	++counter;
		            rs.first();
		            mydata=new Object[counter+1][9];
		            String billDate;
		            int amount,amountsum=0,promotion=0,promotionSum=0;
		            float specialPrice,specialPriceSum=0,billCost,billCostSum=0,credit=0,creditSum=0,cash=0,cashSum=0;
		            java.text.NumberFormat nf = new java.text.DecimalFormat("0.00");
		            //String[] customerCodes = new String[counter];
		            
		            do
		            {	
		            		mydata[i][1]=product;
		            		billDate = rs.getString("bill_date").replaceAll("(\\d+)-(\\d+)-(\\d+)", "$3-$2-$1");
		            		mydata[i][0]=billDate;
		            		//customerCodes[i]=new String();
		            		mydata[i][2]=rs.getString("customer_code");
		            		//customerCodes[i]=(String) mydata[i][2];
		            		//rs2.next();
		            		//mydata[i][2]=rs2.getString("customer_name");
		            		amount=rs.getInt("bill_amount");
		            		amountsum+=amount;
		            		mydata[i][5]=amount;
		            		specialPrice=rs.getFloat("special_price");
		            		specialPriceSum+=specialPrice;
		            		mydata[i][3]=nf.format(specialPrice);
		            		billCost=rs.getFloat("bill_cost");
		            		billCostSum+=billCost;
		            		cash=rs.getFloat("bill_cash");
		            		cashSum+=cash;
		            		credit=billCost-cash;
		            		creditSum+=credit;
		            		mydata[i][8]=nf.format(credit);
		            		mydata[i][7]=nf.format(cash);
		            		mydata[i][6]=nf.format(billCost);
		            		promotion=(int) (((amount*specialPrice)-billCost)/specialPrice);
		            		promotionSum+=promotion;
		            		mydata[i][4]=promotion;
		            		
		            	//	System.out.println(mydata[i][0] + "\t" +mydata[i][1]+"\t"+mydata[i][2]+"\t"+mydata[i][3]+"\t"+mydata[i][4] +"\t" +mydata[i][5]);
		            		++i;
		        
		            }while(rs.next());
		            
		      /*      for(int j=0;j<counter;j++){
		            	System.out.println("customerCode = "+customerCodes[j]);
		            }*/
		            
		            for(int j=0;j<counter;j++){
		            	rs=st.executeQuery("SELECT customer_name from customer WHERE customer_code='"+mydata[j][2]+"'");
		            	rs.next();
		            	mydata[j][2]=rs.getString("customer_name");
		            }
		            
		            
		            mydata[i][0]="Toplam";
		            mydata[i][5]=amountsum;
		            mydata[i][4]=promotionSum;
		            //mydata[i][4]=nf.format(specialPriceSum);
		            mydata[i][6]=nf.format(billCostSum);
		            mydata[i][8]=nf.format(creditSum);
		            mydata[i][7]=nf.format(cashSum);
		            
		            
		            for(int i1=0;i1<mydata.length;i1++)
		            	model.addRow(mydata[i1]);
		           
		        }else {
		            int messageType = JOptionPane.PLAIN_MESSAGE;
		            
		            JOptionPane.showMessageDialog(null, "Böyle bir kayıt yok", "Uyarı!!", messageType);
		            
		        }
		          con.close();  
		        } catch (SQLException ex) {
		            Logger lgr = Logger.getLogger(main.class.getName());
		            lgr.log(Level.SEVERE, ex.getMessage(), ex);

		        } finally {
		            try {
		                if (rs != null) {
		                    rs.close();
		                }
		                if (st != null) {
		                    st.close();
		                }
		                if (con != null) {
		                    con.close();
		                }

		            } catch (SQLException ex) {
		                Logger lgr = Logger.getLogger(main.class.getName());
		                lgr.log(Level.WARNING, ex.getMessage(), ex);
		            }
		        }
		     
			}
		});
	    
	    JLabel newlineLabel = new JLabel("                                                                         " +
	    		"                                                                                                              ");
	    table.setRowHeight(30);
	   
	  	
 	    
	    DefaultTableCellRenderer rdt = new DefaultTableCellRenderer(){
	    	public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected,boolean hasFocus, int row, int column) {
	    		 Component c = 
	    			      super.getTableCellRendererComponent(table, value,
	    			                                          isSelected, hasFocus,
	    			                                          row, column);
	    	if (value == null)
	    	return super.getTableCellRendererComponent(table,
	    	value, isSelected, hasFocus, row, column);
	    	if (row != table.getRowCount()-1 )
	    		c.setFont(new Font("Helvetica Bold", Font.PLAIN,16));
	    	else
	    		c.setFont(new Font("Helvetica Bold", Font.BOLD,16));
	    	
	    	return c;
	    	}
	    	};
		    DefaultTableCellRenderer rightRenderer = new DefaultTableCellRenderer(){
		    	public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected,boolean hasFocus, int row, int column) {
		    		 Component c = 
		    			      super.getTableCellRendererComponent(table, value,
		    			                                          isSelected, hasFocus,
		    			                                          row, column);
		    	if (value == null)
		    	return super.getTableCellRendererComponent(table,
		    	value, isSelected, hasFocus, row, column);
		    	if (row != table.getRowCount()-1 )
		    		c.setFont(new Font("Helvetica Bold", Font.PLAIN,16));
		    	else
		    		c.setFont(new Font("Helvetica Bold", Font.BOLD,16));
		    	
		    	
		    	return c;
		    	}
		    	};
	        TableColumn column = new TableColumn();
	        for (int j = 0; j < columnNames.length; j++) {
	            column = table.getColumnModel().getColumn(j);
	            column.setPreferredWidth(15);
	            if(j<3)
	            	column.setCellRenderer(rdt);
	            else{
	            	rightRenderer.setHorizontalAlignment(SwingConstants.RIGHT);
	            	column.setCellRenderer(rightRenderer);
	            	
	            }

	         
	          
	            
	        }
	        
	     
	    
	       
	    add(newlineLabel);
	    JScrollPane scrollPane = new JScrollPane(table);
	      //Add the scroll pane to this panel.
	    add(scrollPane);
	    JButton PrintTableBtn = new JButton("Yazdır");
	    PrintTableBtn.addActionListener(new ActionListener() {
			
			@Override
			public void actionPerformed(ActionEvent e) {
				  PageFormat documentPageFormat = new PageFormat();
				    documentPageFormat.setOrientation(PageFormat.PORTRAIT);
				    MessageFormat header = new MessageFormat("İki Tarih Arası Ürün Satış Tablosu");
				    try {
				    	//MessageFormat format = new MessageFormat("{0}");
				    	MessageFormat format = new MessageFormat("Sayfa {0,number,integer}");
				        table.print(JTable.PrintMode.FIT_WIDTH, header, format);
				    } catch (java.awt.print.PrinterException e1) {
				        System.err.format("Cannot print %s%n", e1.getMessage());
				    }
			
			}
		});
	    
	    addItem(createTableButton,1,1,1,1,GridBagConstraints.CENTER);
        addItem(PrintTableBtn,2,1,1,1,GridBagConstraints.CENTER);
	  //  
        setVisible(true);
    }
	   
	       
	
	/*public static void main(String[] args) {
		 SortByProduct myInterface=new SortByProduct();
		 myInterface.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		 myInterface.setSize(980, 750);
		 Toolkit kit = myInterface.getToolkit();
		 Dimension screenSize = kit.getScreenSize();
		 int screenWidth = screenSize.width;
		 int screenHeight = screenSize.height;
		 Dimension windowSize = myInterface.getSize();
		 int windowWidth = windowSize.width;
		 int windowHeight = windowSize.height;
		 int upperLeftX = (screenWidth - windowWidth)/2;
		 int upperLeftY = (screenHeight - windowHeight)/2;
		 myInterface.setLocation(upperLeftX, upperLeftY);
		 myInterface.setVisible(true);
		
	}*/
	
	private void addItem(JComponent c, int x, int y, int width, int height, int align) {
        GridBagConstraints gc = new GridBagConstraints();
        gc.gridx = x;
        gc.gridy = y;
        gc.gridwidth = width;
        gc.gridheight = height;
        gc.weightx = 100.0;
        gc.weighty = 100.0;
        gc.insets = new Insets(5, 5, 5, 5);
        gc.anchor = align;
        gc.fill = GridBagConstraints.NONE;
        add(c, gc);
      }


}


